
insert overwrite table jms_dm.dm_lmdm_four_segment_monitoring_sum_dt partition (dt)
select
 t.dt as date_time --日期
,t.sign_finance_code        --签收财务网点编码
,t.sign_finance_name        --签收财务网点名称
,t.sign_network_code        --签收网点code
,t.sign_network_name        --签收网点名称
,t.receiver_province_id     --省份id
,t.receiver_province_name   --省份name
,t.receiver_city_id         --城市id
,t.receiver_city_name       --城市name
,t.receiver_area_id         --区域id
,t.receiver_area_name       --区域name
,t.courier_station_name    --驿站名称
,t.input_type_name         --驿站品牌
,t.courier_station_code            --驿站编码
,t.station_unique_number   --驿站唯一标识
,count(t.waybill_no) as post_count  --驿站总票数
,sum(case when t.plan_station_code is not null then 1 else 0 end  )  as parse_post_count--驿站码解析票数
,sum(case when t.plan_station_code is not null and t.station_code is not null  and t.plan_station_code=t.station_code then 1 else 0 end  )  as true_post_count--驿站码解析票数
,sum( case when t.plan_station_code is not null and t.order_source_code='七星潭'  then 1 else 0 end ) as qxt_parse_count
,sum( case when t.plan_station_code is not null and t.order_source_code='桃花岛'  then 1 else 0 end ) as thd_parse_count
,sum( case when t.plan_station_code is not null and t.order_source_code='云路模型'  then 1 else 0 end ) as yl_parse_count

,sum( case when t.plan_station_code is not null
           and t.station_code is not null
           and t.plan_station_code=t.station_code
           and t.order_source_code='七星潭'  then 1 else 0 end ) as qxt_parse_true_count

,sum( case when t.plan_station_code is not null
            and t.station_code is not null
            and t.plan_station_code=t.station_code
            and t.order_source_code='桃花岛'  then 1 else 0 end ) as thd_parse_true_count

,sum( case when t.plan_station_code is not null
           and t.station_code is not null
           and t.plan_station_code=t.station_code
           and t.order_source_code='云路模型'  then 1 else 0 end ) as yl_parse_true_count

,sum(case when t.order_source_code='七星潭'  then 1 else 0 end ) as qxt_total_count
,sum(case when t.order_source_code='桃花岛'  then 1 else 0 end ) as thd_total_count
,sum(case when t.order_source_code='云路模型'  then 1 else 0 end ) as yl_total_count
,t.station_code
--新增字段
,t.sign_agent_code     --代理区code             
,t.sign_agent_name     --代理区name 
,sum( case when t.plan_station_code is not null and t.order_source_code='紫金山'  then 1 else 0 end ) as zjs_parse_count
,sum( case when t.plan_station_code is not null
           and t.station_code is not null
           and t.plan_station_code=t.station_code
           and t.order_source_code='紫金山'  then 1 else 0 end ) as zjs_parse_true_count
,sum(case when t.order_source_code='紫金山'  then 1 else 0 end ) as zjs_total_count

,t.dt
from jms_dm.dm_lmdm_four_segment_monitoring_detail_dt t
where t.dt='{{ execution_date  | cst_ds }}'
group by 
 t.dt 
,t.sign_finance_code        --签收财务网点编码    
,t.sign_finance_name        --签收财务网点名称   
,t.sign_network_code        --签收网点code    
,t.sign_network_name        --签收网点名称  
,t.receiver_province_id     --省份id
,t.receiver_province_name   --省份name
,t.receiver_city_id         --城市id
,t.receiver_city_name       --城市name
,t.receiver_area_id         --区域id
,t.receiver_area_name       --区域name
,t.courier_station_name    --驿站名称
,t.input_type_name         --驿站品牌
,t.courier_station_code            --驿站编码
,t.station_unique_number   --驿站唯一标识
,t.station_code 
,t.sign_agent_code     --代理区code             
,t.sign_agent_name     --代理区name 

distribute by pmod(hash(rand()),100)
;

